--/
/*
        bom树递归数据查询  公共方法
*/
CREATE PROCEDURE sp_getRecursiveTreeData
        @bomTopCode varchar(50),--母件编号
        @materielCode varchar(50),--物料编号
        --标记  区分是否循环  是否以树状层次结构显示
        @mark varchar(50)       
        as   
        --循环时使用变量      
        declare @bomCode varchar(50) --节点code
        declare @pCode varchar(50)--父级节点编号
        declare @rank varchar(5)--层级
        declare @amount varchar(50)--用量
        declare @counts  varchar(50)       --用量（针对查询物料的使用量）
         create table #tempTreeTable (
                       rank varchar(50), --层级
                       materielCode varchar(50),--物料编号
                       materielName varchar(50),--物料名称
                       bomTopVersion varchar(50),--母件版本号
                       pName varchar(50),--父级物料名称
                       counts  varchar(50),--用量（查询物料）
                       effectiveDate varchar(50),--生效日期
                       expiryDate varchar(50),--失效日期
                       attritionRate varchar(50),--损耗率
                       fixedAttritionRate varchar(50),--损耗量
                       processName varchar(50),--所属工艺流程
                       materialProcess varchar(50),--所属工序
                       basisNum varchar(10),--基数
                       auditStatusName varchar(50),--审核状态
                       clienFeed varchar(50),--是否客户供料
                       manufacturer varchar(50),--生产厂商
                       auditStatusCode varchar(50),--审核状态编号
                       bomCode varchar(50),     --节点code
                       pCode varchar(50),--父级code
                       amount varchar(50),--用量（针对父级）
                       mark varchar(5),--是否为代用物料标记
                        orders  int--排序号
                )  
         declare @orders int--排序号
         set @orders = 0      
         --创建查询物料编号信息游标
        declare c_scrollM cursor for select bomCode ,pCode,rank,amount  from s_bom_info where status ='1' and bomTopCode = @bomTopCode and materielCode = @materielCode
        open c_scrollM 
        --开始循环查询物料信息
        fetch next from c_scrollM into @bomCode,@pCode,@rank,@amount
         while (@@fetch_status=0)           
         begin
         --判断查询结果信息显示方式
                if(@mark = 'single')
              begin
                        set @orders = @orders +1
                        --当查询物料层级为2父级为一阶其父级的父级为母件
                      if(@rank = 2)
                      begin 
                                 insert into  #tempTreeTable select rank,materielCode,materielName,(select bomTopVersion from s_bomTop_info where status = '1' and bomTopCode = @bomTopCode) ,
                                (select materielName from s_bomTop_info where status = '1' and bomTopCode =  @bomTopCode) as ' pName',
                                @amount  as 'counts',effectiveDate,expiryDate,attritionRate,fixedAttritionRate,processName,materialProcess,basisNum,auditStatusName,case clienFeed when '1' then '是' when '0' then '否' else '' end as 'clienFeed' ,manufacturer,auditStatusCode,
                                bomCode,pCode,amount,'y' as 'mark',@orders as 'orders'  from s_bom_info 
                                where status = '1' and bomTopCode = @bomTopCode and bomCode = @pCode                                              
                      end
                      --当查询物料的层级为1，其父级为母件
                      else if (@rank = 1)
                                begin                                 
                                         insert into  #tempTreeTable select '0' as 'rank',materielCode,materielName, bomTopVersion , '' as ' pName',
                                        @amount  as 'counts',effectiveDate,expiryDate,'' as 'attritionRate','' as 'fixedAttritionRate',processName,'' as 'materialProcess',basisNum,
                                        auditStatusName,case clienFeed when '1' then '是' when '0' then '否' else '' end as 'clienFeed','' as 'manufacturer',auditStatusCode,bomTopCode as 'bomCode','' as 'pCode' ,'1' as 'amount' ,'y' as 'mark' ,@orders as 'orders' from s_bomTop_info 
                                        where status = '1' and bomTopCode = @pCode
                                end
                      else
                      begin 
                                 insert into  #tempTreeTable select rank,materielCode,materielName,(select bomTopVersion from s_bomTop_info where status = '1' and bomTopCode = @bomTopCode) ,
                                (select materielName from s_bom_info where status = '1' and bomCode = (select pCode from s_bom_info where status ='1' and bomTopCode = @bomTopCode and bomCode = @pCode )) as ' pName',
                                 @amount as 'counts',effectiveDate,expiryDate,attritionRate,fixedAttritionRate,processName,materialProcess,basisNum,auditStatusName,case clienFeed when '1' then '是' when '0' then '否' else '' end as 'clienFeed',manufacturer,auditStatusCode,bomCode,
                                 pCode,amount,'y' as 'mark' ,@orders as 'orders' from s_bom_info 
                                where status = '1' and bomTopCode = @bomTopCode and bomCode = @pCode
                      end                         
                end
                else
                --多阶信息查询
                begin       
                        --层级为1 其父级为母件                        
                        if(@rank = 1)
                        begin
                                 set @orders = @orders +1     
                        --查询物料为一阶 父级为母件
                                  insert into  #tempTreeTable select '0' as 'rank',materielCode,materielName, bomTopVersion , '' as ' pName',
                                 @amount  as 'counts',effectiveDate,expiryDate,'' as 'attritionRate','' as 'fixedAttritionRate',processName,'' as 'materialProcess',basisNum,
                                 auditStatusName,case clienFeed when '1' then '是' when '0' then '否' else '' end as 'clienFeed','' as 'manufacturer',auditStatusCode,bomTopCode as 'bomCode','' as 'pCode' ,'1' as 'amount','y' as 'mark',@orders as 'orders'  from s_bomTop_info 
                                 where status = '1' and bomTopCode = @pCode
                        end
                        else
                        begin
                        --父级不为母件
                                 declare @a varchar(50)  --存储当前循环阶层针对父级的用量
                                 declare @b varchar(50) --存储查询物料使用情况
                                 set @a = @amount   
                                 set @b = '1'      
                                 declare @num int
                                 set @num = 0       
                                 --声明一个循环当前查询物料的父级及间接父级信息的游标                
                                  declare   c_scrollP cursor for with temp(rank,bomCode,pCode,amount) as(
                                 select rank,bomCode,pCode,amount from s_bom_info where status = '1' and bomCode=@pCode 
                                 union all 
                                 select s.rank,s.bomCode,s.pCode,s.amount  from s_bom_info s inner join temp on temp.pCode = s.bomCode where s.status = '1'   ) 
                                 select * from temp 
                                  union all
                                select '0' as rank,bomTopCode as 'bomCode', '0' as pCode,'1' as amount from s_bomTop_info where bomTopCode = @bomTopCode
                                 --order by rank desc
                                 --打开游标
                                 open c_scrollP
                                 --循环
                                fetch next from c_scrollP into @rank ,@bomCode,@pCode,@amount
                                 while (@@fetch_status = 0)
                                 begin   
                                         set @orders = @orders +1         
                                        print 'rank---' + @rank 
                                        print 'amount ---' + @a
                                        print 'counts----' + @b
                                        if(@num = 0)
                                        begin
                                                 set @b = cast(@a as decimal(10,1))
                                        end
                                        else
                                        begin
                                                 set @b = cast(cast(@b as decimal(10,1)) * cast(@a  as decimal(10,1) ) as decimal(10,1))
                                        end  
                                        --当前循环项为母件                                     
                                        if (@rank = 0)
                                         begin                                                  
                                                 insert into  #tempTreeTable select '0' as 'rank',materielCode,materielName, bomTopVersion , '' as ' pName',
                                                -- cast(@a as decimal(10,1)) 
                                                 @b  as 'counts',effectiveDate,expiryDate,'' as 'attritionRate','' as 'fixedAttritionRate',case processCode when '0' then '' else processName end as 'processName' ,'' as 'materialProcess',basisNum,
                                                 auditStatusName,case clienFeed when '1' then '是' when '0' then '否' else '' end as 'clienFeed','' as 'manufacturer',auditStatusCode,bomTopCode as 'bomCode','' as 'pCode' ,'1' as 'amount','y' as 'mark',@orders as 'orders' from s_bomTop_info 
                                                 where status = '1' and bomTopCode = @bomCode                                                                                            
                                         end
                                         --当前循环项为一阶
                                         else if (@rank = 1)                                        
                                         begin
                                                 if(@mark = 'multi')--阶次是否以树状形式显示
                                                begin
                                                          insert into  #tempTreeTable select right(REPLICATE('.',(rank*3))+CAST(rank AS varchar(50)),((rank+1)*3)) as rank,materielCode,materielName,(select bomTopVersion from s_bomTop_info where status = '1' and bomTopCode = @bomTopCode) ,
                                                          (select materielName from s_bomTop_info where status = '1' and bomTopCode = @pCode ) as ' pName',
                                                          --cast( (cast(@a as decimal(10,1)) * cast(amount as decimal(10,1))) as decimal(10,1))
                                                         @b as 'counts',effectiveDate,expiryDate,attritionRate,fixedAttritionRate,case processCode when '0' then '' else processName end as 'processName',materialProcess,basisNum,auditStatusName, case clienFeed when '1' then '是' when '0' then '否' else '' end as 'clienFeed',manufacturer,
                                                          auditStatusCode,bomCode,pCode,amount ,'y' as 'mark',@orders as 'orders'  from s_bom_info  where status = '1' and bomTopCode = @bomTopCode and bomCode = @bomCode
                                                end 
                                                else
                                                begin
                                                          insert into  #tempTreeTable select rank,materielCode,materielName,(select bomTopVersion from s_bomTop_info where status = '1' and bomTopCode = @bomTopCode) ,
                                                          (select materielName from s_bomTop_info where status = '1' and bomTopCode =@pCode ) as ' pName',
                                                          --cast( (cast(@a as decimal(10,1)) * cast(amount as decimal(10,1))) as decimal(10,1))
                                                         @b as 'counts',effectiveDate,expiryDate,attritionRate,fixedAttritionRate,case processCode when '0' then '' else processName end as 'processName',materialProcess,basisNum,auditStatusName, case clienFeed when '1' then '是' when '0' then '否' else '' end as 'clienFeed',manufacturer,
                                                          auditStatusCode,bomCode,pCode,amount ,'y' as 'mark',@orders as 'orders'  from s_bom_info  where status = '1' and bomTopCode = @bomTopCode and bomCode = @bomCode
                                                end
                                         end   
                                         else                                         
                                         begin
                                                 if(@mark = 'multi')--阶次是否以树状形式显示
                                                begin
                                                          insert into  #tempTreeTable select right(REPLICATE('.',(rank*3))+CAST(rank AS varchar(50)),((rank+1)*3)) as rank,materielCode,materielName,(select bomTopVersion from s_bomTop_info where status = '1' and bomTopCode = @bomTopCode) ,
                                                          (select materielName from s_bom_info where status = '1' and bomCode = @pCode ) as ' pName',
                                                          --cast( (cast(@a as decimal(10,1)) * cast(amount as decimal(10,1))) as decimal(10,1))
                                                         @b as 'counts',effectiveDate,expiryDate,attritionRate,fixedAttritionRate,case processCode when '0' then '' else processName end as 'processName',materialProcess,basisNum,auditStatusName, case clienFeed when '1' then '是' when '0' then '否' else '' end as 'clienFeed',manufacturer,
                                                          auditStatusCode,bomCode,pCode,amount ,'y' as 'mark',@orders as 'orders'  from s_bom_info  where status = '1' and bomTopCode = @bomTopCode and bomCode = @bomCode
                                                end 
                                                else
                                                begin
                                                          insert into  #tempTreeTable select rank,materielCode,materielName,(select bomTopVersion from s_bomTop_info where status = '1' and bomTopCode = @bomTopCode) ,
                                                          (select materielName from s_bom_info where status = '1' and bomCode =@pCode ) as ' pName',
                                                          --cast( (cast(@a as decimal(10,1)) * cast(amount as decimal(10,1))) as decimal(10,1))
                                                         @b as 'counts',effectiveDate,expiryDate,attritionRate,fixedAttritionRate,case processCode when '0' then '' else processName end as 'processName',materialProcess,basisNum,auditStatusName, case clienFeed when '1' then '是' when '0' then '否' else '' end as 'clienFeed',manufacturer,
                                                          auditStatusCode,bomCode,pCode,amount ,'y' as 'mark',@orders as 'orders'  from s_bom_info  where status = '1' and bomTopCode = @bomTopCode and bomCode = @bomCode
                                                end
                                         end
                                       set  @a = @amount
                                       set @num = @num + 1                                                                       
                                        fetch next from c_scrollP into @rank ,@bomCode,@pCode,@amount                                                                        
                                end
                                --关闭当前父级循环游标
                                  close c_scrollP
                                  --释放当前父级循环游标
                                 deallocate c_scrollP
                        end                                                                                
                       -- fetch next from c_scrollM into @bomCode,@pCode,@rank,@amount                      
                end
                 fetch next from c_scrollM into @bomCode,@pCode,@rank,@amount                 
           end    
           --关闭查询物料循环游标  
           close c_scrollM
           --释放查询物料循环游标
           deallocate c_scrollM     
          select * from #tempTreeTable order by orders desc
/
